package DBLayer;
import ModelLayer.Customer;
import java.sql.*;
import java.util.ArrayList;

public class DBCustomer {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBCustomer() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public Customer getCustomerByID(int id) {
        return selectSingle("SELECT `id_customer`, `id_gender`, `secure_key`, `email`, `passwd`, UNIX_TIMESTAMP(`birthday`) AS `birthday`, `lastname`, `newsletter`, `optin`, `firstname`, `active`, `deleted`, UNIX_TIMESTAMP(`date_add`) AS `date_add`, UNIX_TIMESTAMP(`date_upd`) AS `date_upd` FROM `ps_customer` WHERE `id_customer` = " + id + " LIMIT 0, 1;");
    }

    public ArrayList<Customer> getCustomerList() {
        return selectMultiple("SELECT `id_customer`, `id_gender`, `secure_key`, `email`, `passwd`, UNIX_TIMESTAMP(`birthday`) AS `birthday`, `lastname`, `newsletter`, `optin`, `firstname`, `active`, `deleted`, UNIX_TIMESTAMP(`date_add`) AS `date_add`, UNIX_TIMESTAMP(`date_upd`) AS `date_upd` FROM `ps_customer` ORDER BY `id_customer` ASC;");
    }

    public ArrayList<Integer> getCustomerIDsByGroupID(int id) {
        return selectIDs("SELECT `id_customer` FROM `ps_customer_group` WHERE `id_group` = " + id + " ORDER BY `id_customer` ASC;");
    }

    private Customer selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        Customer customer = new Customer();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                customer = buildCustomer(result);
            }
            System.out.println("Fetching Customer Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return customer;
    }

    private ArrayList<Customer> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Customer> customers = new ArrayList<Customer>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                Customer customer = new Customer();
                customer = buildCustomer(result);
                customers.add(customer);
            }
            System.out.println("Fetching Customer List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return customers;
    }

    private ArrayList<Integer> selectIDs(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Integer> ids = new ArrayList<Integer>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                ids.add(result.getInt("id_customer"));
            }
            System.out.println("Fetching Customer ID List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return ids;
    }

    private Customer buildCustomer(ResultSet result) {
        DBGroup dbGroup = new DBGroup();
        Customer customer = new Customer();
        try {
            customer.setIDCustomer(result.getInt("id_customer"));
            customer.setIDGender(result.getInt("id_gender"));
            customer.setSecureKey(result.getString("secure_key"));
            customer.setEmail(result.getString("email"));
            customer.setPasswd(result.getString("passwd"));
            customer.setBirthday(result.getInt("birthday"));
            customer.setLastname(result.getString("lastname"));
            customer.setFirstname(result.getString("firstname"));
            customer.setDateAdd(result.getInt("date_add"));
            customer.setDateUpd(result.getInt("date_upd"));
            if (result.getInt("newsletter") == 1) {
                customer.setNewsletter(true);
            } else {
                customer.setNewsletter(false);
            }
            if (result.getInt("optin") == 1) {
                customer.setOptin(true);
            } else {
                customer.setOptin(false);
            }
            if (result.getInt("active") == 1) {
                customer.setActive(true);
            } else {
                customer.setActive(false);
            }
            if (result.getInt("deleted") == 1) {
                customer.setDeleted(true);
            } else {
                customer.setDeleted(false);
            }
            customer.setGroupIDs(dbGroup.getGroupIDsByCustomerID(result.getInt("id_customer")));

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return customer;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addCustomer(Customer customer) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;
      
        String query = "INSERT INTO `ps_customer` (`id_gender`, `secure_key`, `email`, "
                + "`passwd`, `birthday`, `lastname`, `firstname`, `newsletter`, `optin`, `active`, `deleted`, `date_add`, `date_upd`) "
                + "VALUES (" + customer.getIDGender() + ", '" + customer.getSecureKey() + "', '" + customer.getEmail() + "', "
                + "'" + customer.getPasswd() + "', FROM_UNIXTIME(" + customer.getBirthday() + "), '" + customer.getLastname() + "', '" + customer.getFirstname() + "', ";
        if (customer.getNewsletter() == true)   { query += "1, "; }
        else                                    { query += "0, "; }
        if (customer.getOptin() == true)        { query += "1, "; }
        else                                    { query += "0, "; }
        if (customer.getActive() == true)       { query += "1, "; }
        else                                    { query += "0, "; }
        if (customer.getDeleted() == true)      { query += "1, "; }
        else                                    { query += "0, "; }
        query += "FROM_UNIXTIME(" + customer.getDateAdd() + "), FROM_UNIXTIME(" + customer.getDateUpd() + "));";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added Customer ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateCustomer(Customer customer, int id) {
        int returnID = 0;
        Statement stmt = null;
        String query = "UPDATE `ps_customer` SET "
                + "`id_customer`    = "  + customer.getIDCustomer() + ", "
                + "`id_gender`      = "  + customer.getIDGender()   + ", "
                + "`secure_key`     = '" + customer.getSecureKey()  + "', "
                + "`email`          = '" + customer.getEmail()      + "', "
                + "`passwd`         = '" + customer.getPasswd()     + "', "
                + "`birthday`       = FROM_UNIXTIME("  + customer.getBirthday()   + "), "
                + "`lastname`       = '" + customer.getLastname()   + "', "
                + "`firstname`      = '" + customer.getFirstname()  + "', ";
        if (customer.getNewsletter() == true)   { query += "`newsletter` = 1, "; }
        else                                    { query += "`newsletter` = 0, "; }
        if (customer.getOptin() == true)        { query += "`optin` = 1, "; }
        else                                    { query += "`optin` = 0, "; }
        if (customer.getActive() == true)       { query += "`active` = 1, "; }
        else                                    { query += "`active` = 0, "; }
        if (customer.getDeleted() == true)      { query += "`deleted` = 1, "; }
        else                                    { query += "`deleted` = 0, "; }
        query  += "`date_add`       = FROM_UNIXTIME("  + customer.getDateAdd()    + "), "
                + "`date_upd`       = FROM_UNIXTIME("  + customer.getDateUpd()    + ") "
                + "WHERE `id_customer` = " + id + " LIMIT 1;";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = customer.getIDCustomer();
            System.out.println("Updated Customer ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteCustomer(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_customer` WHERE `id_customer` = " + id + " LIMIT 1;");
            System.out.println("Deleted Customer ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}